package com.example.erat.util;

import com.example.erat.constant.SystemConstant;
import com.example.erat.dto.StatisticsDTO;
import com.example.erat.dto.StudentDTO;
import com.example.erat.exception.FileParseException;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

/**
 * Excel工具类
 * 
 * 质量属性:
 * - 可扩展性(E1): 支持添加新的文件格式解析器
 * - 可维护性(M1): 文件解析逻辑与业务逻辑分离
 * - 可靠性(R1): 全面的异常检测和处理
 */
@Slf4j
public class ExcelUtil {

    /**
     * 解析Excel文件导入学生
     * 
     * 支持质量属性:
     * - 可用性(U1): 支持批量导入学生数据
     * - 可靠性(R1): 处理各种文件格式错误
     * 
     * @param file Excel文件
     * @return 学生列表
     */
    public static List<StudentDTO> importStudents(MultipartFile file) {
        try {
            return getExcelParser(file).parseStudents(file.getInputStream());
        } catch (IOException e) {
            log.error("解析Excel文件失败", e);
            throw new FileParseException("解析Excel文件失败: " + e.getMessage(), e);
        }
    }
    
    /**
     * 导出学生维度统计结果到Excel
     * 
     * 支持质量属性:
     * - 可用性(U1): 提供统计结果导出功能
     * - 性能(P1): 高效处理统计数据
     * 
     * @param statistics 学生维度统计结果
     * @return Excel文件字节数组
     */
    public static byte[] exportStudentStatistics(List<StatisticsDTO.StudentStatistics> statistics) {
        try (Workbook workbook = new XSSFWorkbook();
             ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
            
            Sheet sheet = workbook.createSheet("学生缺交统计");
            
            // 创建表头
            Row headerRow = sheet.createRow(0);
            headerRow.createCell(0).setCellValue(SystemConstant.ExportExcelColumn.STUDENT_ID);
            headerRow.createCell(1).setCellValue(SystemConstant.ExportExcelColumn.STUDENT_NAME);
            headerRow.createCell(2).setCellValue(SystemConstant.ExportExcelColumn.MISSING_COUNT);
            headerRow.createCell(3).setCellValue(SystemConstant.ExportExcelColumn.MISSING_EXPERIMENTS);
            
            // 填充数据
            int rowNum = 1;
            for (StatisticsDTO.StudentStatistics statistic : statistics) {
                Row row = sheet.createRow(rowNum++);
                row.createCell(0).setCellValue(statistic.getStudentId());
                row.createCell(1).setCellValue(statistic.getName());
                row.createCell(2).setCellValue(statistic.getMissingCount());
                row.createCell(3).setCellValue(String.join(", ", statistic.getMissingExperiments()));
            }
            
            // 自动调整列宽
            for (int i = 0; i < 4; i++) {
                sheet.autoSizeColumn(i);
            }
            
            workbook.write(outputStream);
            return outputStream.toByteArray();
            
        } catch (IOException e) {
            log.error("导出Excel失败", e);
            throw new FileParseException("导出Excel失败: " + e.getMessage(), e);
        }
    }
    
    /**
     * 导出实验维度统计结果到Excel
     * 
     * 支持质量属性:
     * - 可用性(U1): 提供统计结果导出功能
     * - 性能(P1): 高效处理统计数据
     * 
     * @param statistics 实验维度统计结果
     * @return Excel文件字节数组
     */
    public static byte[] exportExperimentStatistics(List<StatisticsDTO.ExperimentStatistics> statistics) {
        try (Workbook workbook = new XSSFWorkbook();
             ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
            
            Sheet sheet = workbook.createSheet("实验缺交统计");
            
            // 创建表头
            Row headerRow = sheet.createRow(0);
            headerRow.createCell(0).setCellValue(SystemConstant.ExportExcelColumn.EXPERIMENT_NAME);
            headerRow.createCell(1).setCellValue(SystemConstant.ExportExcelColumn.MISSING_STUDENTS_COUNT);
            headerRow.createCell(2).setCellValue(SystemConstant.ExportExcelColumn.SUBMISSION_RATE);
            headerRow.createCell(3).setCellValue(SystemConstant.ExportExcelColumn.MISSING_STUDENTS_LIST);
            
            // 填充数据
            int rowNum = 1;
            for (StatisticsDTO.ExperimentStatistics statistic : statistics) {
                Row row = sheet.createRow(rowNum++);
                row.createCell(0).setCellValue(statistic.getExperimentName());
                row.createCell(1).setCellValue(statistic.getMissingCount());
                row.createCell(2).setCellValue(String.format("%.2f%%", statistic.getSubmissionRate() * 100));
                row.createCell(3).setCellValue(String.join(", ", statistic.getMissingStudents()));
            }
            
            // 自动调整列宽
            for (int i = 0; i < 4; i++) {
                sheet.autoSizeColumn(i);
            }
            
            workbook.write(outputStream);
            return outputStream.toByteArray();
            
        } catch (IOException e) {
            log.error("导出Excel失败", e);
            throw new FileParseException("导出Excel失败: " + e.getMessage(), e);
        }
    }
    
    /**
     * 获取Excel解析器 - 工厂模式
     * 
     * 支持质量属性:
     * - 可扩展性(E1): 可以轻松添加新的文件格式解析器
     * - 可维护性(M1): 将文件类型检测与解析逻辑分离
     * - 可靠性(R1): 对不支持的文件格式提供明确错误信息
     * 
     * @param file 文件
     * @return Excel解析器
     */
    private static ExcelParser getExcelParser(MultipartFile file) {
        String filename = file.getOriginalFilename();
        if (filename == null) {
            throw new FileParseException("文件名不能为空");
        }
        
        if (filename.endsWith(SystemConstant.FileConstant.EXCEL_SUFFIX)) {
            return new XlsxExcelParser();
        } else if (filename.endsWith(SystemConstant.FileConstant.CSV_SUFFIX)) {
            return new CsvExcelParser();
        } else {
            throw new FileParseException("不支持的文件格式：" + filename + "，只支持xlsx和csv格式");
        }
    }
    
    /**
     * Excel解析器接口 - 工厂模式
     * 
     * 支持质量属性:
     * - 可扩展性(E1): 可以添加新的解析器实现
     */
    private interface ExcelParser {
        List<StudentDTO> parseStudents(InputStream inputStream) throws IOException;
    }
    
    /**
     * XLSX解析器
     */
    private static class XlsxExcelParser implements ExcelParser {
        
        @Override
        public List<StudentDTO> parseStudents(InputStream inputStream) throws IOException {
            List<StudentDTO> students = new ArrayList<>();
            
            try (Workbook workbook = WorkbookFactory.create(inputStream)) {
                Sheet sheet = workbook.getSheetAt(0);
                
                // 解析表头
                Row headerRow = sheet.getRow(0);
                if (headerRow == null) {
                    throw new FileParseException("Excel文件为空或缺少表头");
                }
                Map<String, Integer> headerMap = new HashMap<>();
                for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                    Cell cell = headerRow.getCell(i);
                    if (cell != null) {
                        headerMap.put(cell.getStringCellValue(), i);
                    }
                }
                
                // 检查必要的列
                if (!headerMap.containsKey(SystemConstant.ExcelColumn.STUDENT_ID) ||
                    !headerMap.containsKey(SystemConstant.ExcelColumn.NAME)) {
                    throw new FileParseException("Excel文件格式不正确，必须包含学号和姓名列");
                }
                
                // 解析数据行
                for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                    Row row = sheet.getRow(i);
                    if (row == null) continue;
                    
                    StudentDTO student = new StudentDTO();
                    
                    // 获取学号
                    int studentIdIdx = headerMap.get(SystemConstant.ExcelColumn.STUDENT_ID);
                    Cell studentIdCell = row.getCell(studentIdIdx);
                    if (studentIdCell == null) {
                        log.warn("第{}行学号为空，跳过", i + 1);
                        continue;
                    }
                    String studentId = getCellValueAsString(studentIdCell);
                    student.setStudentId(studentId);
                    
                    // 获取姓名
                    int nameIdx = headerMap.get(SystemConstant.ExcelColumn.NAME);
                    Cell nameCell = row.getCell(nameIdx);
                    if (nameCell == null) {
                        log.warn("第{}行姓名为空，跳过", i + 1);
                        continue;
                    }
                    student.setName(getCellValueAsString(nameCell));
                    
                    // 获取年级（可选）
                    if (headerMap.containsKey(SystemConstant.ExcelColumn.GRADE)) {
                        int gradeIdx = headerMap.get(SystemConstant.ExcelColumn.GRADE);
                        Cell gradeCell = row.getCell(gradeIdx);
                        if (gradeCell != null) {
                            student.setGrade(getCellValueAsString(gradeCell));
                        }
                    }
                    
                    // 获取专业（可选）
                    if (headerMap.containsKey(SystemConstant.ExcelColumn.MAJOR)) {
                        int majorIdx = headerMap.get(SystemConstant.ExcelColumn.MAJOR);
                        Cell majorCell = row.getCell(majorIdx);
                        if (majorCell != null) {
                            student.setMajor(getCellValueAsString(majorCell));
                        }
                    }
                    
                    students.add(student);
                }
            }
            
            return students;
        }
        
        private String getCellValueAsString(Cell cell) {
            switch (cell.getCellType()) {
                case STRING:
                    return cell.getStringCellValue();
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        return cell.getDateCellValue().toString();
                    } else {
                        double value = cell.getNumericCellValue();
                        if (value == (long) value) {
                            return String.valueOf((long) value);
                        } else {
                            return String.valueOf(value);
                        }
                    }
                case BOOLEAN:
                    return String.valueOf(cell.getBooleanCellValue());
                case FORMULA:
                    return cell.getCellFormula();
                default:
                    return "";
            }
        }
    }
    
    /**
     * CSV解析器
     */
    private static class CsvExcelParser implements ExcelParser {
        
        @Override
        public List<StudentDTO> parseStudents(InputStream inputStream) throws IOException {
            List<StudentDTO> students = new ArrayList<>();
            
            try (Scanner scanner = new Scanner(inputStream)) {
                if (!scanner.hasNextLine()) {
                    throw new FileParseException("CSV文件为空");
                }
                
                // 解析表头
                String headerLine = scanner.nextLine();
                String[] headers = headerLine.split(",");
                Map<String, Integer> headerMap = new HashMap<>();
                for (int i = 0; i < headers.length; i++) {
                    headerMap.put(headers[i].trim(), i);
                }
                
                // 检查必要的列
                if (!headerMap.containsKey(SystemConstant.ExcelColumn.STUDENT_ID) ||
                    !headerMap.containsKey(SystemConstant.ExcelColumn.NAME)) {
                    throw new FileParseException("CSV文件格式不正确，必须包含学号和姓名列");
                }
                
                // 解析数据行
                int lineNum = 1;
                while (scanner.hasNextLine()) {
                    lineNum++;
                    String line = scanner.nextLine();
                    if (line.trim().isEmpty()) continue;
                    
                    String[] values = line.split(",");
                    if (values.length < headerMap.size()) {
                        log.warn("第{}行数据不完整，跳过", lineNum);
                        continue;
                    }
                    
                    StudentDTO student = new StudentDTO();
                    
                    // 获取学号
                    int studentIdIdx = headerMap.get(SystemConstant.ExcelColumn.STUDENT_ID);
                    if (studentIdIdx >= values.length || values[studentIdIdx].trim().isEmpty()) {
                        log.warn("第{}行学号为空，跳过", lineNum);
                        continue;
                    }
                    student.setStudentId(values[studentIdIdx].trim());
                    
                    // 获取姓名
                    int nameIdx = headerMap.get(SystemConstant.ExcelColumn.NAME);
                    if (nameIdx >= values.length || values[nameIdx].trim().isEmpty()) {
                        log.warn("第{}行姓名为空，跳过", lineNum);
                        continue;
                    }
                    student.setName(values[nameIdx].trim());
                    
                    // 获取年级（可选）
                    if (headerMap.containsKey(SystemConstant.ExcelColumn.GRADE)) {
                        int gradeIdx = headerMap.get(SystemConstant.ExcelColumn.GRADE);
                        if (gradeIdx < values.length && !values[gradeIdx].trim().isEmpty()) {
                            student.setGrade(values[gradeIdx].trim());
                        }
                    }
                    
                    // 获取专业（可选）
                    if (headerMap.containsKey(SystemConstant.ExcelColumn.MAJOR)) {
                        int majorIdx = headerMap.get(SystemConstant.ExcelColumn.MAJOR);
                        if (majorIdx < values.length && !values[majorIdx].trim().isEmpty()) {
                            student.setMajor(values[majorIdx].trim());
                        }
                    }
                    
                    students.add(student);
                }
            }
            
            return students;
        }
    }
} 